﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.Net;
using System.Web;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;
 

namespace SFast.DB
{
    /// <summary>
    /// 使用前准备：1、在webconfig中connectionStrings下添加结点    默认去抓匀 name="constring" 的值做连接数据库字符串
    /// 结点一：name="xxx" 存储连接数据库字符串
    /// 结点三：name=dbtype 表示所在连接的数据：值：0为mysql  1：mssql2005以上  2：mssql2000 
    /// </summary>
    public class DbExecuter
    {

        #region 数据库类属性
        /// <summary>
        /// Connection 对象
        /// </summary>
        public DbConnection conn { get; set; }

        /// <summary>
        /// Command 对象
        /// </summary>
        public DbCommand Com { get; set; }

        /// <summary>
        /// DataAdapter 对象
        /// </summary>
        public DbDataAdapter da { get; set; }

        /// <summary>
        /// CommandBuilder 对象
        /// </summary>
        public DbCommandBuilder cb { get; set; }

        /// <summary>
        /// Web.Config中connectionStrings结点 name=dbtype值   0:mysql 1:mssql2005以上 2：mssql2000
        /// </summary>
        public int ConnType { get; set; }

        #endregion

        #region 数据库连接初始化
        /// <summary>
        /// 按web.config中connectionStrings节点中 name=ConnectionName的值来存储数据连接字符串
        /// </summary>
        public DbExecuter()
        {
            GetAll("constring");
        }

        /// <summary>
        /// 按web.config中connectionStrings节点Name的值来存储数据连接字符串
        /// </summary>
        /// <param name="name">connectionStrings 结点Name</param>
        public DbExecuter(string name)
        {
            GetAll(name);
        }

        private void GetAll(string name)
        {
            string conntemp = "";
            
            if (string.IsNullOrEmpty(name))
            {
                name = "constring";
            }
            conntemp = ConfigurationManager.ConnectionStrings[name].ToString();  //获取连接字符串
            var ConfigConnType = ConfigurationManager.ConnectionStrings["dbtype"];  //获取数据类型
            ConnType = Convert.ToInt32(ConfigConnType==null ? "1" : ConfigConnType.ToString());
            //conntemp = ConfigurationManager.AppSettings[name].ToString();

            this.GetConn();
            this.GetCom();
            this.GetDataAdapter();
            conn.ConnectionString = conntemp;
            Com.Connection = conn;
            da.SelectCommand = Com;
            //Open();
        }
        #endregion

        #region 初始化ado对象
        private void GetConn()
        {
            if (ConnType == 0)
            {
                conn = new MySqlConnection();
            }
            else if (ConnType == 1 || ConnType == 2)
            {
                conn = new SqlConnection();
            }
        }

        private void GetCom()
        {
            if (ConnType == 0)
            {
                Com = new MySqlCommand();
            }
            else if (ConnType == 1 || ConnType == 2)
            {
                Com = new SqlCommand();

            }
        }

        private void GetDataAdapter()
        {
            if (ConnType == 0)
            {
                da = new MySqlDataAdapter();
            }
            else if (ConnType == 1 || ConnType == 2)
            {
                da = new SqlDataAdapter();
            }
        }

        private void GetCb()
        {
            if (ConnType == 0)
            {
                cb = new MySqlCommandBuilder();
            }
            else if (ConnType == 1 || ConnType == 2)
            {
                cb = new SqlCommandBuilder();
            }
        }
        #endregion

        #region 打开数据连接
        /// <summary>
        /// 打开数据连接
        /// </summary>
        private void Open()
        {
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    throw new Exception("数据库连接超时，可能是网络不通或线程池已满。<br />原始错误提示：" + e.Message);
                }
            }
        }
        #endregion

        #region  数据库相关操作函数

        #region 返回一个reader
        /// <summary>
        /// 返回一个reader
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DbDataReader ExecReader(string sql)
        {
            Com.CommandText = sql;
            if (conn.State != System.Data.ConnectionState.Open)
            {
                conn.Open();
            }
            DbDataReader dr = Com.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }
        #endregion

        #region  返回第一行第一个字段的值
        /// <summary>
        /// 返回第一行第一个字段的值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public string Exec_Solo(string sql)
        {
            Com.CommandText = sql;
            Open();
            string result = "";
            try
            {
                if (Com.ExecuteScalar() != null)
                {
                    result = Com.ExecuteScalar().ToString();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + "\n" + sql);
            }
            Close();
            return result;
        }
        #endregion

        #region 返回DataTable结果集
        /// <summary>
        /// 返回DataTable结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable Exec_DataTable(string sql)
        {
            DataTable dt = new DataTable();
            Com.CommandText = sql;
            da.Fill(dt);
            return dt;
        }
        #endregion

        #region 执行带参数的sql语句，返回DataTable结果集  sql server 版本
        /// <summary>
        /// 执行带参数的sql语句，返回DataTable结果集   sql server 版本
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="SqlParameter">SqlParameter的数组</param>
        /// <returns></returns>
        public DataTable Exec_DataTable(string sql, SqlParameter[] SqlParameter)
        {
            DataTable dt = new DataTable();
            if (SqlParameter != null)
            {
                Com.Parameters.Clear();
                foreach (SqlParameter item in SqlParameter)
                {
                    Com.Parameters.Add(item);
                }
                Com.CommandType = CommandType.Text;
            }
            Com.CommandText = sql;
            da.Fill(dt);
            return dt;
        }
        #endregion

        #region 执行带参数的sql语句，返回DataTable结果集 mysql 版本
        /// <summary>
        /// 执行带参数的sql语句，返回DataTable结果集   mysql 版本
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="SqlParameter">MySqlParameter的数组</param>
        /// <returns></returns>
        public DataTable Exec_DataTable(string sql, MySqlParameter[] Parameter)
        {
            DataTable dt = new DataTable();
            if (Parameter != null)
            {
                Com.Parameters.Clear();
                foreach (MySqlParameter item in Parameter)
                {
                    Com.Parameters.Add(item);
                }
                Com.CommandType = CommandType.Text;
            }
            Com.CommandText = sql;
            da.Fill(dt);
            return dt;
        }
        #endregion

        #region 返回DataSet结果集
        /// <summary>
        /// 返回DataSet结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataSet Exec_DataSet(string sql)
        {
            DataSet ds = new DataSet();
            Com.CommandText = sql;
            da.Fill(ds);
            return ds;
        }
        #endregion

        #region 执行SQL语句，返回影响行数
        /// <summary> 
        /// 执行SQL语句，返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        public int Exec_NonQuery(string sql)
        {
            return ExecNo(sql, new SqlParameter[] { }, 0);
        }
        #endregion

        #region 执行带参数的Sql语句，返回影响行数
        /// <summary>
        /// 执行带参数的Sql语句，返回影响行数   SqlServer 版本
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        public int Exec_NonQuery(string sql, SqlParameter[] parameters)
        {
            return ExecNo(sql, parameters, 0);
        }

        /// <summary>
        /// 执行带参数的Sql语句，返回影响行数   MySql 版本
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        public int Exec_NonQuery(string sql, MySqlParameter[] parameters)
        {
            return ExecNo(sql, parameters, 0);
        }

        #region 私有函数  执行带参数的Sql语句，返回影响行数
        /// <summary>
        ///  执行带参数的Sql语句，返回影响行数   SqlServer 版本
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="flag"></param>
        /// <returns></returns>
        private int ExecNo(string sql, SqlParameter[] parameters, int flag)
        {
            if (flag == 0)
            {
                Open();
            }
            if (parameters != null)
            {
                Com.Parameters.Clear();
                for (int i = 0; i < parameters.Length; i++)
                {
                    Com.Parameters.Add(parameters[i]);
                }
                Com.CommandType = CommandType.Text;
            }

            Com.CommandText = sql;

            int result = 0;
            try
            {
                result = Com.ExecuteNonQuery();
            }
            finally
            {
                if (flag == 0)
                {
                    Close();
                }
            }

            return result;
        }


        /// <summary>
        ///  执行带参数的Sql语句，返回影响行数   MySql 版本
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="flag"></param>
        /// <returns></returns>
        private int ExecNo(string sql, MySqlParameter[] parameters, int flag)
        {
            if (flag == 0)
            {
                Open();
            }
            if (parameters != null)
            {
                Com.Parameters.Clear();
                for (int i = 0; i < parameters.Length; i++)
                {
                    Com.Parameters.Add(parameters[i]);
                }
                Com.CommandType = CommandType.Text;
            }

            Com.CommandText = sql;

            int result = 0;
            try
            {
                result = Com.ExecuteNonQuery();
            }
            finally
            {
                if (flag == 0)
                {
                    Close();
                }
            }

            return result;
        }
        #endregion
        #endregion

        #region 执行存储过程 ，返回DataTable 数据
        /// <summary>
        /// 执行存储过程 ，返回DataTable 数据
        /// </summary>
        /// <param name="SP_Name">存储过程名称</param>
        /// <param name="par">参数集合</param>
        /// <returns></returns>
        public DataTable Exec_SP_DataTable(string SP_Name, SqlParameter[] par)
        {
            DataTable dt = new DataTable();
            Open();
            Com.CommandText = SP_Name;
            Com.CommandType = CommandType.StoredProcedure;
            if (par != null)
            {
                Com.Parameters.Clear();
                for (int i = 0; i < par.Length; i++)
                {
                    Com.Parameters.Add(par[i]);
                }
            }
            da.Fill(dt);
            Com.CommandType = CommandType.Text;
            return dt;
        }
        #endregion

        #region 执行一条存储过程 返回存储过程的返回值
        /// <summary>
        /// 执行一条存储过程 返回存储过程的返回值
        /// </summary>
        /// <param name="SP_Name">存储过程 名称 </param>
        /// <param name="par">参数集合</param>
        /// <returns></returns>
        public string Exec_SP_Query(string SP_Name, SqlParameter[] par)
        {
            DataTable dt = new DataTable();
            Open();
            Com.CommandText = SP_Name;
            Com.CommandType = CommandType.StoredProcedure;

            if (par != null)
            {
                Com.Parameters.Clear();
                for (int i = 0; i < par.Length; i++)
                {
                    Com.Parameters.Add(par[i]);
                }
            }
            // 创建参数
            IDataParameter[] parameters = {
                new SqlParameter("return", SqlDbType.Int,4)
            };
            // 将参数类型设置为 返回值类型
            parameters[0].Direction = ParameterDirection.ReturnValue;
            Com.Parameters.Add(parameters[0]);
            Com.ExecuteNonQuery();
            Com.CommandType = CommandType.Text;
            return parameters[0].Value.ToString();
        }
        #endregion

        #region 使用DataTable插入表中的数据
        /// <summary>
        /// 使用DataTable插入中的数据
        /// </summary>
        /// <param name="dt">要更新至数据库的DataTable</param>
        /// <param name="dt">对应的数据库表名</param>
        public int IntoDb(DataTable dt, string TableName)
        {
            GetCb();
            cb.DataAdapter = da;
            if (ConnType == 1 || ConnType == 2)
            {
                Com.CommandText = "select top 0 * from " + TableName;
            }
            else if (ConnType == 0)
            {
                Com.CommandText = "select * from " + TableName + " limit 0";
            }
            da.SelectCommand = Com;
            return da.Update(dt);
        }
        #endregion

        #region 获取数据集并分页

        #region 通过sql进行分页，并获取分页数据
        /// <summary>
        /// 通过sql进行分页，并获取分页数据
        /// </summary>
        /// <param name="sql">要分页的sql语句(不可有order by )</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="orderStr">排序语句  1:order by xx  2: order by xx desc  sql2000:order by A_CreateTime|desc|asc</param>
        public Pagination Exec_Pager(string sql, int pagesize, string orderStr)
        {

            Pagination page = null;
            try
            {
                int PageNow = 1;
                if (HttpContext.Current.Request.QueryString["page"] != null)
                {
                    PageNow = Convert.ToInt32(HttpContext.Current.Request.QueryString["page"].Replace(",", "").Replace("&", "").Replace("?", ""));
                }

                sql = sql.Trim().ToLower();
                sql = Regex.Replace(sql, "\\s+", " ");

                String CountSql = GetCountSql(sql);            //获取查看总数量sql语句

                int RowCount = Convert.ToInt32(this.Exec_Solo(CountSql));   //查询数据总行数

                //获取当前页的sql语句
                String DataSql = GetPagedSql(sql, PageNow, pagesize, orderStr);

                //获取当前页数据
                DataTable Data = this.Exec_DataTable(DataSql);
                page = new Pagination(Data, PageNow , pagesize, RowCount);
                
            }
            catch (Exception e)
            {
                throw e;
            }
            return page;

        }

        /// <summary>
        /// 通过sql进行分页，并获取泛型分页数据
        /// </summary>
        /// <param name="sql">要分页的sql语句(不可有order by )</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="orderStr">排序语句  1:order by xx  2: order by xx desc  sql2000:order by A_CreateTime|desc|asc</param>
        public Pagination<T> Exec_Pager<T>(string sql, int pagesize, string orderStr) where T:class,new()
        {

            Pagination<T> page = null;
            try
            {
                int PageNow = 1;
                if (HttpContext.Current.Request.QueryString["page"] != null)
                {
                    PageNow = Convert.ToInt32(HttpContext.Current.Request.QueryString["page"].Replace(",", "").Replace("&", "").Replace("?", ""));
                }

                sql = sql.Trim().ToLower();
                sql = Regex.Replace(sql, "\\s+", " ");

                String CountSql = GetCountSql(sql);            //获取查看总数量sql语句

                int RowCount = Convert.ToInt32(this.Exec_Solo(CountSql));   //查询数据总行数

                //获取当前页的sql语句
                String DataSql = GetPagedSql(sql, PageNow, pagesize, orderStr);
                
                //获取当前页数据
                DataTable Data = this.Exec_DataTable(DataSql);
                page = Data._ToPagination<T>(pagesize, RowCount);

            }
            catch (Exception e)
            {
                throw e;
            }
            return page;

        }

        #endregion

        #region 返回分页列表的html代码，2012.8.1 挪移到sfast库
//        /// <summary>
//        /// 返回分页列表的html代码
//        /// (2011.8.23日修改,反回分页字符串,放入css样式为(.common_pagebody)容器中,并结合pageUI才可得到效果)
//        /// (2011.12.9日修改，返css样式为(.common_pagebody)容器封装到代码中)
//        /// (2011.12.9日修改，添加JS翻页，默认为url，如用js翻页，要在使用前设置 JumpType属性和 JsFunName属性)
//        /// </summary>
//        /// <returns>分页码字符串</returns>
//        private string PageList()
//        {
//            string url = HttpContext.Current.Request.Url.PathAndQuery;
//            StringBuilder temp = new StringBuilder();
//            if (this._Language.Equals("cn"))
//            {
//                if (PageCount > 0)
//                {
//                    temp.Append("<div class='common_pagebody'>");
//                    int ShowSize = this.Padding;  //控制分页,当前页两边显示多少个页码,其它用省略号代替 例如 : 当前页为8时 1...5 6 7 8 9 10 11...30   8左边显示3个右边显示3个   

//                    #region 上一页 和 设置第一页
//                    if (PageNow > 1)
//                    {
//                        temp.Append(GetTempHtml(PageNow - 1, "Previous", "上一页", true));           //当前页不为1的时： 上一页
//                        temp.Append(GetTempHtml(1, "MinPage", "1", true));                           //当前页不为1的时： 第一页
//                    }
//                    else
//                    {
//                        //当前页为第一页时不可用
//                        temp.Append(GetTempHtml(1, "Previous Enabled", "上一页", false));
//                    }
//                    #endregion

//                    #region 中间部分
//                    if (PageNow - ShowSize > 2)
//                    {
//                        temp.Append("<span class='points'>...</span>");
//                        for (int i = ShowSize; i > 0; i--)
//                        {
//                            temp.Append(GetTempHtml(PageNow - i, "", (PageNow - i).ToString(), true));
//                        }
//                    }
//                    else
//                    {
//                        for (int i = 2; i < PageNow; i++)
//                        {
//                            temp.Append(GetTempHtml(i, "", i.ToString(), true));
//                        }
//                    }

//                    //当前页
//                    temp.Append(GetTempHtml(PageNow, "CurrentPage", PageNow.ToString(), false));




//                    //当前页后的页号处理
//                    if (PageNow + ShowSize < PageCount - 1)
//                    {
//                        for (int i = 1; i < ShowSize + 1; i++)
//                        {
//                            temp.Append(GetTempHtml(PageNow + i, "", (PageNow + i).ToString(), true));
//                        }
//                        temp.Append("<span class='points'>...</span>");
//                    }
//                    else
//                    {
//                        for (int i = PageNow + 1; i < PageCount; i++)
//                        {
//                            temp.Append(GetTempHtml(i, "", i.ToString(), true));
//                        }
//                    }

//                    #endregion

//                    #region 下一页  和 最后一页
//                    if (PageNow < PageCount)
//                    {
//                        temp.Append(GetTempHtml(PageCount, "", PageCount.ToString(), true));
//                        temp.Append(GetTempHtml(PageNow + 1, "NextPage", "下一页", true));
//                    }
//                    else
//                    {
//                        temp.Append(GetTempHtml(PageCount, "NextPage Enabled", "下一页", false));
//                    }
//                    #endregion

//                    #region (共 多少 条)、转到 多少 页 、跳转按钮
//                    if (this._ShowCountStr)
//                    {
//                        temp.Append("<span class='Rows_Count'>(共 " + this.RowCount + " 条)</span>");
//                    }
//                    //判断是否要显示这个跳转
//                    if (this._ShowJump)
//                    {
//                        switch (this.JumpType)
//                        {
//                            case "url":
//                                temp.Append(" <span class='jump'>转到<input name=page type=text id=pagejump size=3/>页</span><input class='Page_Bt_Go' type=button onclick=\"pageJump(document.getElementById('pagejump').value);\" value=Go />");
//                                break;
//                            case "js":  //如果需用此属性需要导入Common.js 和Function1.js文件
//                                temp.Append(" <span class='jump'>转到<input name=page type=text id=pagejump size=3/>页</span><input class='Page_Bt_Go' type=button onclick=\"" + this.JsFunName + "(document.getElementById('pagejump').value);\" value=Go />");       //如果用JS的方式 跳转也要跳到
//                                break;
//                        }
//                    }
//                    temp.Append("<div style='clear:both'></div> </div>");
//                    //temp.Append(@"<script language=javascript>function pageJump(value){ var urls=location.href;var CanShuStr='';var CanShuJiHe= new Array(); if (value >" + PageCount + " || value < 1) {alert('输入有误！请输入范围为 1 ~ " + PageCount + "的数字！'); return false;}if(urls.indexOf('?')>-1){ CanShuStr=urls.split('?')[1];  if (CanShuStr.indexOf('page')>-1){ if(CanShuStr.indexOf('&')>-1){ CanShuJiHe=CanShuStr.split('&');}else{  CanShuJiHe[0]=CanShuStr;}}else{urls=urls+'&page='+value;} var x; for(x in CanShuJiHe){ var canshu=CanShuJiHe[x]; if(canshu.indexOf('page')>-1){urls=urls.replace(canshu,'page='+value);}}}else{urls=urls+'?page='+value;} if (value==1) { urls = urls.replace('?page=1&', '?').replace('?page=1', '').replace('&page=1&', '&').replace('&page=1', ''); } location.href=urls;}</script>");
//                    temp.Append(@"<script language=javascript> 
//                                var pageJump = function(value) {
//                                    var urls = location.href;
//                                    var CanShuStr = '';
//                                    var CanShuJiHe = new Array();
//                                    var reg = /\D+/;
//                                    if (!reg.test(value) == false || value == '')
//                                    { alert('请填写有效的数字字符！'); return false; }
//");
//                    temp.Append("        if (value > " + PageCount + " || value < 1) {");
//                    temp.Append("                       alert('输入有误！请输入范围为 1 ~ " + PageCount + "的数字！'); return false;");
//                    temp.Append("                   }");
//                    temp.Append(@" if (urls.indexOf('?') > -1) {
//                                        CanShuStr = urls.split('?')[1];
//                                        if (CanShuStr.indexOf('page') > -1) {
//                                            if (CanShuStr.indexOf('&') > -1) { CanShuJiHe = CanShuStr.split('&'); } else { CanShuJiHe[0] = CanShuStr; }
//                                        } else {
//                                            urls = urls + '&page=' + value;
//                                        }
//                                        var x;
//                                        for (x in CanShuJiHe) {
//                                            var canshu = CanShuJiHe[x];
//                                            if (canshu.indexOf('page') > -1) {
//                                                urls = urls.replace(canshu, 'page=' + value);
//                                            }
//                                        }
//                                    } else {
//                                        urls = urls + '?page=' + value;
//                                    }
//                                    if (value == 1) {
//                                        urls = urls.replace('?page=1&', '?').replace('?page=1', '').replace('&page=1&', '&').replace('&page=1', '');
//                                    }
//                                    location.href = urls;
//                                } 
//                            </script>");
//                    #endregion
//                }
//            }
//            else if (this._Language.Equals("en"))
//            {
//                if (PageCount > 0)
//                {
//                    temp.Append("<div class='common_pagebody'>");
//                    int ShowSize = this.Padding;  //控制分页,当前页两边显示多少个页码,其它用省略号代替 例如 : 当前页为8时 1...5 6 7 8 9 10 11...30   8左边显示3个右边显示3个   

//                    #region 上一页 和 设置第一页
//                    if (PageNow > 1)
//                    {
//                        temp.Append(GetTempHtml(PageNow - 1, "Previous", "Previous", true));           //当前页不为1的时： 上一页
//                        temp.Append(GetTempHtml(1, "MinPage", "1", true));                           //当前页不为1的时： 第一页
//                    }
//                    else
//                    {
//                        //当前页为第一页时不可用
//                        temp.Append(GetTempHtml(1, "Previous Enabled", "Previous", false));
//                    }
//                    #endregion

//                    #region 中间部分
//                    if (PageNow - ShowSize > 2)
//                    {
//                        temp.Append("<span class='points'>...</span>");
//                        for (int i = ShowSize; i > 0; i--)
//                        {
//                            temp.Append(GetTempHtml(PageNow - i, "", (PageNow - i).ToString(), true));
//                        }
//                    }
//                    else
//                    {
//                        for (int i = 2; i < PageNow; i++)
//                        {
//                            temp.Append(GetTempHtml(i, "", i.ToString(), true));
//                        }
//                    }

//                    //当前页
//                    temp.Append(GetTempHtml(PageNow, "CurrentPage", PageNow.ToString(), false));




//                    //当前页后的页号处理
//                    if (PageNow + ShowSize < PageCount - 1)
//                    {
//                        for (int i = 1; i < ShowSize + 1; i++)
//                        {
//                            temp.Append(GetTempHtml(PageNow + i, "", (PageNow + i).ToString(), true));
//                        }
//                        temp.Append("<span class='points'>...</span>");
//                    }
//                    else
//                    {
//                        for (int i = PageNow + 1; i < PageCount; i++)
//                        {
//                            temp.Append(GetTempHtml(i, "", i.ToString(), true));
//                        }
//                    }

//                    #endregion

//                    #region 下一页  和 最后一页
//                    if (PageNow < PageCount)
//                    {
//                        temp.Append(GetTempHtml(PageCount, "", PageCount.ToString(), true));
//                        temp.Append(GetTempHtml(PageNow + 1, "NextPage", "Next", true));
//                    }
//                    else
//                    {
//                        temp.Append(GetTempHtml(PageCount, "NextPage Enabled", "Next", false));
//                    }
//                    #endregion

//                    #region (共 多少 条)、转到 多少 页 、跳转按钮
//                    if (this._ShowCountStr)
//                    {
//                        temp.Append("<span class='Rows_Count'>(Total " + this.RowCount + " Documents )</span>");
//                    }
//                    //判断是否要显示这个跳转
//                    if (this._ShowJump)
//                    {
//                        switch (this.JumpType)
//                        {
//                            case "url":
//                                temp.Append(" <span class='jump'>Move to<input name=page type=text id=pagejump size=3/></span><input class='Page_Bt_Go' type=button onclick=\"pageJump(document.getElementById('pagejump').value);\" value=Go />");
//                                break;
//                            case "js":  //如果需用此属性需要导入Common.js 和Function1.js文件
//                                temp.Append(" <span class='jump'>Move to<input name=page type=text id=pagejump size=3/></span><input class='Page_Bt_Go' type=button onclick=\"" + this.JsFunName + "(document.getElementById('pagejump').value);\" value=Go />");       //如果用JS的方式 跳转也要跳到
//                                break;
//                        }
//                    }
//                    temp.Append("<div style='clear:both'></div> </div>");
//                    //temp.Append(@"<script language=javascript>function pageJump(value){ var urls=location.href;var CanShuStr='';var CanShuJiHe= new Array(); if (value >" + PageCount + " || value < 1) {alert('输入有误！请输入范围为 1 ~ " + PageCount + "的数字！'); return false;}if(urls.indexOf('?')>-1){ CanShuStr=urls.split('?')[1];  if (CanShuStr.indexOf('page')>-1){ if(CanShuStr.indexOf('&')>-1){ CanShuJiHe=CanShuStr.split('&');}else{  CanShuJiHe[0]=CanShuStr;}}else{urls=urls+'&page='+value;} var x; for(x in CanShuJiHe){ var canshu=CanShuJiHe[x]; if(canshu.indexOf('page')>-1){urls=urls.replace(canshu,'page='+value);}}}else{urls=urls+'?page='+value;} if (value==1) { urls = urls.replace('?page=1&', '?').replace('?page=1', '').replace('&page=1&', '&').replace('&page=1', ''); } location.href=urls;}</script>");
//                    temp.Append(@"<script language=javascript> 
//                                var pageJump = function(value) {
//                                    var urls = location.href;
//                                    var CanShuStr = '';
//                                    var CanShuJiHe = new Array();
//                                    var reg = /\D+/;
//                                    if (!reg.test(value) == false || value == '')
//                                    { alert('Please input a number.'); return false; }
//");
//                    temp.Append("        if (value > " + PageCount + " || value < 1) {");
//                    temp.Append("                       alert('Wrong value, please input a number between 1 ~ " + PageCount + ".'); return false;");
//                    temp.Append("                   }");
//                    temp.Append(@" if (urls.indexOf('?') > -1) {
//                                        CanShuStr = urls.split('?')[1];
//                                        if (CanShuStr.indexOf('page') > -1) {
//                                            if (CanShuStr.indexOf('&') > -1) { CanShuJiHe = CanShuStr.split('&'); } else { CanShuJiHe[0] = CanShuStr; }
//                                        } else {
//                                            urls = urls + '&page=' + value;
//                                        }
//                                        var x;
//                                        for (x in CanShuJiHe) {
//                                            var canshu = CanShuJiHe[x];
//                                            if (canshu.indexOf('page') > -1) {
//                                                urls = urls.replace(canshu, 'page=' + value);
//                                            }
//                                        }
//                                    } else {
//                                        urls = urls + '?page=' + value;
//                                    }
//                                    if (value == 1) {
//                                        urls = urls.replace('?page=1&', '?').replace('?page=1', '').replace('&page=1&', '&').replace('&page=1', '');
//                                    }
//                                    location.href = urls;
//                                } 
//                            </script>");
//                    #endregion
//                }
//            }
//            return temp.ToString();
//        }
        #endregion

        #region 得到分页后的查询数据库的sql语句
        /// <summary>
        /// 得到分页后的查询数据库的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="page">当前页数</param>
        /// <param name="pagesize">当前页显示的数量</param>
        /// <param name="orderstr">数据人排序</param>
        /// <returns></returns>
        private string GetPagedSql(string sql, int page, int pagesize, string orderstr)
        {
            string sqltemp = sql;
            if (this.ConnType == 0)  //mysql
            {
                string s = Convert.ToString((page - 1) * pagesize);
                string e = Convert.ToString(pagesize);
                sqltemp = sqltemp + " limit " + s + "," + e;
            }
            else if (this.ConnType == 1) //sql2005以上
            {
                sqltemp = Regex.Replace(sqltemp, "\\ +", " ");
                string s = Convert.ToString(((page - 1) * pagesize) + 1);
                string e = Convert.ToString(page * pagesize);

                sqltemp = "select (ROW_NUMBER() OVER(" + orderstr + ")) AS ThisRowNo,* from (" + sqltemp + ") AS tb1";
                sqltemp = "select * from (" + sqltemp + ") AS TempDBTable WHERE ThisRowNo BETWEEN " + s + " AND " + e;
            }
            else if (this.ConnType == 2) //sql 2000
            {
                sqltemp = Regex.Replace(sqltemp, "\\ +", " ");

                sqltemp = "select top " + (page * pagesize) + sqltemp.Substring(sqltemp.IndexOf("select", StringComparison.CurrentCultureIgnoreCase) + 6);
                //sqltemp = "select top " + (page * pagesize > this.RowCount ? this.RowCount - (page - 1) * pagesize : pagesize) + " * from (" + sqltemp + ") As tb1 " + orderstr.Split('|')[0] + " " + orderstr.Split('|')[1];
                sqltemp = "select * from (" + sqltemp + ") as tb2 " + orderstr.Split('|')[0] + " " + orderstr.Split('|')[2]; ;
            }
            return sqltemp;
        }
        #endregion

        #region 得到计算总行数的sql语句
        private string GetCountSql(string sql)
        {
            if (this.ConnType == 0)
            {
                //sql = Regex.Replace(sql, "(select) (.*) (?<s1>from .*?(\\(select .*\\))?.*?)", "select count(*) $1",RegexOptions.ExplicitCapture);
                //sql = Regex.Replace(sql, "select .*? from (.*)", "select count(*) from $1");
                sql = "select count(*) from( " + sql + " ) as table1";
            }
            else if (this.ConnType == 1)
            {
                //sql = Regex.Replace(sql, "select (?<s1>.*?(\\(select.*?from.*?\\))?.*?) (?<s2>from .*?(\\(select.*?from.*?\\))?.*?) (?<s3>order by .*)", "select count(*) $2", RegexOptions.ExplicitCapture);
                //sql = Regex.Replace(sql, "select (?<s1>.*?(\\(select.*?from.*?\\))?.*?) (?<s2>from .*?(\\(select.*?from.*?\\))?.*?) (?<s3>order by .*)", "select count(*) $2", RegexOptions.ExplicitCapture);
                sql = "select count(*) from (" + sql + ") as tb";
            }
            else if (this.ConnType == 2)
            {
                sql = sql.Replace(sql.Substring(sql.LastIndexOf(" order ", StringComparison.CurrentCultureIgnoreCase)), "");
                sql = "select count(*) from (" + sql + ") as tb";
            }
            return sql;
        }
        #endregion
        #endregion


        #endregion

        #region 自动收集表单数据
        /*
        /// <summary>
        /// 自动收集表单数据
        /// </summary>
        /// <param name="TableName">要更新的表名</param>
        /// <param name="NoStr">要过滤掉的参数名</param>
        public void AutoFormInDb(string TableName,string NoStr,StringPair[] Oth) 
        {
            HttpRequest Request = HttpContext.Current.Request;
            DataTable dt;
            DataRow dr;
            NoStr = "|id|submit|button|action|imageField|imageField.x|imageField.y|x|y|" + NoStr + "|";
            int flag = 0;
            if (Request.Params["id"] != null)
            {
                if (Request.Params["id"] != "")
                {
                    //有id，属于更新操作
                    flag = 1;
                }
            }
            dt = new DataTable();
            if (ConnType == 0)
            {
                dt = Exec("select * from " + TableName + " limit 0");
            }
            else if (ConnType == 1)
            {
                dt = Exec("select top 0 * from " + TableName);
            }

            dr = dt.NewRow();
            if (flag == 1)
            {
                dt = new DataTable();
                if (ConnType == 0)
                {
                    dt = Exec("select * from " + TableName + " where id='" + StrCommon.UrlCheck(Request.Params["id"]) + "' limit 1");
                }
                else if (ConnType == 1)
                {                   
                    dt = Exec("select top 1 * from " + TableName + " where id='" + StrCommon.UrlCheck(Request.Params["id"]) + "'");
                }
                dr = dt.Rows[0];
            }

            #region 接收Form和QueryString集合中的参数
            string pars = "";
            try
            {
                for (int i = 0; i < Request.QueryString.Count; i++)
                {
                    pars = pars + Request.QueryString.Keys[i].ToString() + " | ";
                    if (NoStr.ToLower().IndexOf("|" + Request.QueryString.Keys[i].ToString().ToLower() + "|") < 0)
                    {
                        dr[Request.QueryString.Keys[i].ToString()] = Request.QueryString[i].ToString();
                    }
                }
                pars = pars + "________";
                for (int i = 0; i < Request.Form.Count; i++)
                {
                    pars = pars + Request.Form.Keys[i].ToString() + " | ";
                    if (NoStr.IndexOf("|" + Request.Form.Keys[i].ToString() + "|") < 0)
                    {
                        dr[Request.Form.Keys[i].ToString()] = Request.Form[i].ToString();
                    }
                }
            }
            catch(Exception e)
            {
                throw new Exception(pars + "收集form信息出错，可能的原因是传入的值与字段类型不符合\n" + e.Message);
            }
            #endregion
			
            #region 处理需要单独设置的参数
            if (Oth!=null)
            {
                foreach ( StringPair x in Oth )
                {
                    dr[x.Title] = x.Info;
                }
            }
            #endregion

            if (flag == 0)
            {
                dt.Rows.Add(dr);
            }

            #region 调用IntoDb
            //try
            //{
                IntoDb(dt, TableName);
            //}
            //catch
            //{
            //	throw new Exception("更新数据库出错。除了以下原因外，还可能是"+TableName+"表没有设置主键。");
            //}
            #endregion
        }
       
        /// <summary>
        /// 简化版，默认过滤|id|submit|button|action|
        /// </summary>
        /// <param name="TableName">要更新的表名</param>
        public void AutoFormInDb(string TableName)
        {
            AutoFormInDb(TableName, "|id|submit|button|action|imageField|imageField.x|imageField.y|x|y|myrefer|checkbox|", null);
        }
         */
        /*
        /// <summary>
        /// 简化版，带特殊字段设置
        /// </summary>
        /// <param name="TableName">要更新的表名</param>
        public void AutoFormInDb(string TableName, StringPair[] Oth)
        {
            AutoFormInDb(TableName, "|id|submit|button|action|imageField|imageField.x|imageField.y|x|y|myrefer|checkbox|", Oth);
        }
        */
        #region 把结果集里的数据填写到表单
        /// <summary>
        /// 把结果集里的数据填写到表单
        /// </summary>
        /// <param name="dt">数据集</param>
        /// <param name="formname">表单名字</param>
        /// <param name="FuncName"></param>
        /// <returns></returns>
        public string AutoFormOutPut(DataTable dt, string formname, string FuncName)
        {
            if (FuncName == "")
            {
                FuncName = "AutoFormIn";
            }
            string Js = "<script language=javascript>" + Environment.NewLine;
            if (dt.Rows.Count > 0)
            {

                Js = Js + "function " + FuncName + "(){" + Environment.NewLine;
                int i;
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    Js = Js + "setFormValue('" + dt.Columns[i].Caption + "','" + dt.Rows[0][i].ToString().Replace("'", "\\'").Replace(Environment.NewLine, " ").Replace("\r", "").Replace("\n", "") + "','" + formname + "');" + Environment.NewLine;
                }
                Js = Js + "}" + Environment.NewLine;
                Js = Js + FuncName + "();" + Environment.NewLine;
                Js = Js + "</script>" + Environment.NewLine;
            }
            else
            {
                Js = "";
            }
            return Js;
        }

        /// <summary>
        /// 把结果集里的数据填写到表单，默认表单名formX
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string AutoFormOutPut(DataTable dt)
        {
            return AutoFormOutPut(dt, "formX", "");
        }


        public string AutoFormOutPut(DataTable dt, string FuncName)
        {
            return AutoFormOutPut(dt, "formX", FuncName);
        }

        public string AutoFormOutPut(string sql, string formname)
        {
            return AutoFormOutPut(this.Exec_DataTable(sql), formname, "");
        }
        #endregion
        #endregion

        #region  执行sql数组事务
        /// <summary>
        /// 执行sql数组事务
        /// </summary>
        public string ExecTransaction(string[] sql)
        {
            string result = "";
            Exec_NonQuery("begin tran");
            try
            {
                foreach (string s in sql)
                {
                    Exec_NonQuery(s);
                }

                Exec_NonQuery("COMMIT");
            }
            catch (Exception e)
            {
                Exec_NonQuery("ROLLBACK");
                result = "事务出错原因：" + e.Message;
            }
            finally
            {
                Close();
            }

            return result;
        }
        #endregion

        #region  销毁对象
        /// <summary>
        /// 销毁对象
        /// </summary>
        public void Close()
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
            //conn.Dispose();
            //da.Dispose();
            //dc.Dispose();
        }
        #endregion
    }
}

